from typing import List, Union

from sqlmodel import select
from sqlmodel.sql._expression_select_cls import Select, SelectOfScalar

from src.models.example.member_model import MemberModel
from src.resource.database import get_async_session
from src.services.base import BaseService
from src.types.schema.example.member import MemberResp, MemberReq, MemberCreateReq, MemberUpdateReq
from src.utils.pager import Pager


class MemberService(BaseService):

    async def create(self, req: MemberCreateReq) -> None | MemberResp:
        """创建"""
        async with get_async_session() as session:
            member = MemberModel.model_validate(req)
            session.add(member)
            await session.commit()
            await session.refresh(member)
            return MemberResp.model_validate(member)

    async def update(self, req: MemberUpdateReq) -> None | MemberResp:
        """更新"""
        async with get_async_session() as session:
            member = await session.get(MemberModel, req.id)
            if not member:
                return None
            member.name = req.name
            member.email = req.email
            session.add(member)
            await session.commit()
            await session.refresh(member)
            return MemberResp.model_validate(member)

    async def delete(self, member_id: int) -> None:
        """删除"""
        async with get_async_session() as session:
            member = await session.get(MemberModel, member_id)
            if not member:
                return None
            await session.delete(member)
            await session.commit()
            return None

    async def one(self, member_id: int) -> None | MemberResp:
        """获取单个"""
        async with get_async_session() as session:
            member = await session.get(MemberModel, member_id)
            if not member:
                return None
            return MemberResp.model_validate(member)

    async def all(self) -> None | List[MemberResp]:
        """获取全部"""
        async with get_async_session() as session:
            # 查询
            result = await session.execute(select(MemberModel))
            members = result.scalars().all()
            # 转化为 List[MemberResp]
            return [MemberResp.model_validate(member) for member in members]

    async def query(self, req: MemberReq) -> None | List[MemberResp]:
        """分页查询"""
        async with get_async_session() as session:
            statement = self.statement(req).offset(Pager.offset(req.page, req.limit)).limit(req.limit)
            result = await session.execute(statement)
            members = result.scalars().all()
            return [MemberResp.model_validate(member) for member in members]

    def statement(self, req: MemberReq) -> Union[Select, SelectOfScalar]:
        """
        查询构造器
        :param req:
        :return:
        """
        """
        查询构造器

        Args:
            req (MemberReq): 查询体

        Returns:
            Union[Select, SelectOfScalar]: 查询对象
        """
        statement = select(MemberModel)
        if req.id is not None and req.id > 0:
            statement = statement.where(MemberModel.id == req.id)
        if req.name is not None and len(req.name) > 0:
            statement = statement.where(MemberModel.name == req.name)
        if req.email is not None and len(req.email) > 0:
            statement = statement.where(MemberModel.email == req.email)
        return statement
